package com.briup.jdbc;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.*;

/**
 * 对比Statement和PreparedStatement的效率
 */
public class Test7 {
    public static final String driver = "com.mysql.cj.jdbc.Driver";
    public static final String url = "jdbc:mysql:///db01?useServerPrepStmts=true";
    public static final String username = "root";
    public static final String password = "rootroot";

    public static long start;
    public static long end;

    @Test
    public void st(){
        Connection connection = null;
        Statement statement = null;

        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,username,password);
            statement = connection.createStatement();
            for(int i =3;i<10003;i++){
                String sql = "insert into t_user values("+i+",'tom"+i+"','123',20)";
                statement.execute(sql);
            }

            statement.close();
            connection.close();

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @Test
    public void pst(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,username,password);
            String sql = "insert into t_user values(?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            for(int i =3;i<10003;i++){
                preparedStatement.setInt(1,i);
                preparedStatement.setString(2,"tom"+i);
                preparedStatement.setString(3,"123");
                preparedStatement.setInt(4,20);
                // 加入批处理，每隔1000条提交一次
                preparedStatement.addBatch();
                if(i% 5000 == 0){
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
            }
            // 剩下的不能被1000整除的，最后提交一次
             preparedStatement.executeBatch();

            preparedStatement.close();
            connection.close();

        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Before
    public void before(){
        start = System.currentTimeMillis();
    }
    @After
    public void after(){
        end = System.currentTimeMillis();
        System.out.println("共耗时："+(end-start)+"毫秒");
    }
}
